﻿using System;
using System.Collections.Generic;
using System.Data;
using Dapper;
using BCEntity.TMS.Driver.DriverExamine;
using BCCommon;
using System.Threading.Tasks;

namespace BCData.TMS.Driver.DriverExamine
{
    public class DriverExamineMarkData : IDriverExamineMarkData
    {
        private const string JOIN_QUERY_SQL = @"SELECT dem.ExamineMarkId,dem.PaperId,dem.DriverId,dem.Mark,dem.CorrectNumber,dem.ErrorNumber,dem.CreateTime,d.VehicleNo,d.VehicleType,d.DriverLicense,d.VehicleLicense,d.DriverPhotoPath,d.VehiclePhotoFrontPath,d.VehiclePhotoBackPath,d.CarInsurancePolicy,d.Income,d.DriverStatus,d.IsTrainPassed,d.TrainPassedTime,d.CreateTime as DriverCreateTime,dep.CorrectAmount,dep.ErrorAmount,dep.CreateTime as PaperCreateTime,dep.EndTime as PaperEndTime  FROM DriverExamineMark dem LEFT JOIN Driver d on dem.DriverId=d.DriverId LEFT JOIN DriverExaminePapers dep on dem.PaperId=dep.PaperId ";
        private const string QUERY_SQL = @"SELECT ExamineMarkId,PaperId,DriverId,CreateTime,Mark,CorrectNumber,ErrorNumber  FROM DriverExamineMark ";

        private readonly IDatabaseContext databaseContext;
        public DriverExamineMarkData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }

        /// <summary>
        /// 新增司机考试成绩
        /// </summary>
        /// <param name="entity">司机考试成绩</param>
        /// <returns></returns>
        public async Task<long> InsertAsync(DriverExamineMarkEntity entity)
        {
            string sql = @"INSERT INTO DriverExamineMark(DriverId,Mark,PaperId,CorrectNumber,ErrorNumber,CreateTime) VALUES(?DriverId,?Mark,?PaperId,?CorrectNumber,?ErrorNumber,?CreateTime); SELECT LAST_INSERT_ID();";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DriverId", entity.DriverId, DbType.Int64);
            parameter.Add("?PaperId", entity.PaperId, DbType.Int64);
            parameter.Add("?CorrectNumber", entity.CorrectNumber, DbType.Int32);
            parameter.Add("?ErrorNumber", entity.ErrorNumber, DbType.Int32);
            parameter.Add("?CreateTime", DateTime.Now, DbType.DateTime);
            parameter.Add("?Mark", entity.Mark, DbType.Int32);
            var result = await this.databaseContext.Master.ExecuteScalarAsync<int>(sql, parameter);
            return result;
        }
        /// <summary>
        /// 检查司机Id是否在使用
        /// </summary>
        /// <param name="examineMarkId">考试成绩Id</param>
        /// <returns></returns>
        public async Task<bool> CheckDriverIdInUsedAsync(long examineMarkId)
        {
            string sql = @"SELECT d.DriverId FROM DriverExamineMark dem LEFT JOIN Driver d ON dem.DriverId = dem.DriverId  WHERE ExamineMarkId =? ExamineMarkId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ExamineMarkId", examineMarkId, DbType.Int64);
            var result = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameter);
            return result != null;
        }

        /// <summary>
        /// 检查考试成绩是否存在
        /// </summary>
        /// <param name="examineMarkId">考试成绩Id</param>
        /// <returns></returns>
        public async Task<bool> CheckExistsByIdAsync(long examineMarkId)
        {
            string sql = @"SELECT 1 FROM DriverExamineMark WHERE ExamineMarkId=?ExamineMarkId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ExamineMarkId", examineMarkId, DbType.Int64);
            var result = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameter);
            return result != null;
        }

        /// <summary>
        /// 检查考试试卷是否被使用
        /// </summary>
        /// <param name="examineMarkId">考试成绩Id</param>
        /// <returns></returns>
        public async Task<bool> CheckPaperIdInUsedAsync(long examineMarkId)
        {
            string sql = @"SELECT dep.PaperId FROM DriverExamineMark dem LEFT JOIN DriverExaminePapers dep ON dem.PaperId = dep.PaperId  WHERE ExamineMarkId =? ExamineMarkId LIMIT 1";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ExamineMarkId", examineMarkId, DbType.Int64);
            var result = await this.databaseContext.Master.ExecuteScalarAsync(sql, parameter);
            return result != null;
        }
        /// <summary>
        /// 删除司机考试成绩
        /// </summary>
        /// <param name="examineMarkId">考试成绩Id</param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(long examineMarkId)
        {
            string sql = @"DELETE FROM DriverExamineMark WHERE ExamineMarkId=?ExamineMarkId";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ExamineMarkId", examineMarkId, DbType.Int64);
            var i = await this.databaseContext.Master.ExecuteAsync(sql, parameter);
            return i > 0;
        }

        /// <summary>
        /// 获取考试成绩
        /// </summary>
        /// <param name="examineMarkId">考试成绩Id</param>
        /// <returns></returns>
        public async Task<DriverExamineMarkEntity> GetDriverExamineMarkByIdAsync(long examineMarkId)
        {
            string sql = string.Format(@"{0} WHERE dem.ExamineMarkId=?ExamineMarkId", JOIN_QUERY_SQL);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?ExamineMarkId", examineMarkId, DbType.Int32);
            return await this.databaseContext.Master.QueryEntityAsync<DriverExamineMarkEntity>(sql, parameter);
        }

        /// <summary>
        /// 分页查询考试成绩列表
        /// </summary>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="pageSize">分页页数</param>
        /// <returns></returns>
        public async Task<(IEnumerable<DriverExamineMarkEntity>, int)> GetDriverExamineMarksAsync(int pageIndex, int pageSize)
        {
            pageIndex = pageIndex == 0 ? 1 : pageIndex;
            int startIndex = (pageIndex - 1) * pageSize;
            string sql = string.Format(@"{0}  LIMIT {1} , {2}; SELECT COUNT(1) FROM DriverExamineMark;", JOIN_QUERY_SQL, startIndex, pageSize);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?startIndex", startIndex, DbType.Int32);
            parameter.Add("?pageSize", pageSize, DbType.Int32);

            return await this.databaseContext.Master.QueryMultipleAsync<DriverExamineMarkEntity, int>(sql, parameter);
        }
        /// <summary>
        /// 更新司机考试成绩
        /// </summary>
        /// <param name="entity">司机考试成绩</param>
        /// <returns></returns>
        public async Task<DriverExamineMarkEntity> UpdateAsync(DriverExamineMarkEntity entity)
        {
            string updateSql = @"UPDATE DriverExamineMark SET DriverId=?DriverId,PaperId=?PaperId,CorrectNumber=?CorrectNumber,ErrorNumber=?ErrorNumber,Mark=?Mark WHERE ExamineMarkId=?ExamineMarkId";
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DriverId", entity.DriverId, DbType.Int64);
            parameter.Add("?PaperId", entity.PaperId, DbType.Int64);
            parameter.Add("?CorrectNumber", entity.CorrectNumber, DbType.Int32);
            parameter.Add("?ErrorNumber", entity.ErrorNumber, DbType.Int32);
            parameter.Add("?Mark", entity.Mark, DbType.Int32);
            parameter.Add("?ExamineMarkId", entity.ExamineMarkId, DbType.Int64);
            string sql = string.Format("{0};{1}", updateSql, QUERY_SQL);
            return await this.databaseContext.Master.QueryEntityAsync<DriverExamineMarkEntity>(sql, parameter);
        }

        /// <summary>
        /// 获取考试成绩
        /// </summary>
        /// <param name="driverId">司机Id</param>
        /// <returns></returns>
        public async Task<DriverExamineMarkEntity> GetExamineMarkByDriverIdAsync(long driverId)
        {
            string sql = string.Format("{0} WHERE DriverId=?DriverId LIMIT 1", QUERY_SQL);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DriverId", driverId, DbType.Int64);
            return await this.databaseContext.Master.QueryEntityAsync<DriverExamineMarkEntity>(sql, parameter);
        }

        /// <summary>
        /// 司机端如果司机考试失败，重新考试后更新成绩
        /// </summary>
        /// <returns></returns>
        public async Task<DriverExamineMarkEntity> UpdateMarkAsync(DriverExamineMarkEntity entity)
        {
            string sql = string.Format(@"UPDATE DriverExamineMark SET PaperId=?PaperId,CorrectNumber=?CorrectNumber,ErrorNumber=?ErrorNumber,Mark=?Mark 
                    WHERE ExamineMarkId=?ExamineMarkId AND DriverId=?DriverId;{0}  WHERE ExamineMarkId=?ExamineMarkId AND DriverId=?DriverId;", QUERY_SQL);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?DriverId", entity.DriverId, DbType.Int64);
            parameter.Add("?PaperId", entity.PaperId, DbType.Int64);
            parameter.Add("?CorrectNumber", entity.CorrectNumber, DbType.Int32);
            parameter.Add("?ErrorNumber", entity.ErrorNumber, DbType.Int32);
            parameter.Add("?Mark", entity.Mark, DbType.Int32);
            parameter.Add("?ExamineMarkId", entity.ExamineMarkId, DbType.Int64);
            return await this.databaseContext.Master.QueryEntityAsync<DriverExamineMarkEntity>(sql, parameter);
        }
    }
}
